import mysql from 'mysql2/promise';

export interface DorisConnectionConfig {
  host: string;
  port: number;
  user: string;
  password: string;
  database?: string | undefined;
  ssl?: boolean | string | mysql.SslOptions | undefined;
  timeout?: number;
  acquireTimeout?: number;
  connectionLimit?: number;
}

export interface QueryResult {
  rows: any[];
  fields: any[];
  affectedRows?: number;
  insertId?: number;
  warningCount?: number;
}

export interface TableSchema {
  table_name: string;
  columns: Array<{
    column_name: string;
    data_type: string;
    is_nullable: boolean;
    column_default: string | null;
    column_comment: string;
  }>;
}

export interface DatabaseSchema {
  database_name: string;
  tables: Array<{
    table_name: string;
    table_type: string;
    table_comment: string;
  }>;
}

export class DorisConnector {
  private pool: mysql.Pool | null = null;
  private config: DorisConnectionConfig;

  constructor(config: DorisConnectionConfig) {
    this.config = {
      ...config,
      timeout: config.timeout || 30000,
      acquireTimeout: config.acquireTimeout || 60000,
      connectionLimit: config.connectionLimit || 10
    };
  }

  // 创建连接池
  private createPool(): mysql.Pool {
    if (this.pool) {
      return this.pool;
    }

    const poolConfig: any = {
      host: this.config.host,
      port: this.config.port,
      user: this.config.user,
      password: this.config.password,
      queueLimit: 0,
      // Doris特定配置
      supportBigNumbers: true,
      bigNumberStrings: true,
      dateStrings: true,
      multipleStatements: false, // 安全考虑，禁用多语句执行
      charset: 'utf8mb4'
    };

    // Add optional properties if specified
    if (this.config.database) {
      poolConfig.database = this.config.database;
    }
    
    if (this.config.ssl !== undefined) {
      poolConfig.ssl = this.config.ssl === true ? {} : (this.config.ssl === false ? undefined : this.config.ssl);
    }
    
    if (this.config.connectionLimit) {
      poolConfig.connectionLimit = this.config.connectionLimit;
    }
    
    if (this.config.timeout) {
      poolConfig.timeout = this.config.timeout;
    }

    this.pool = mysql.createPool(poolConfig);

    // 监听连接池事件
    this.pool.on('connection', (connection) => {
      console.log(`Doris连接池新建连接: ${(connection as any).threadId}`);
    });

    // Note: mysql2 Pool doesn't have 'error' event, handle errors in individual operations

    return this.pool;
  }



  // 测试连接
  async testConnection(): Promise<{
    success: boolean;
    message: string;
    responseTime?: number;
    serverVersion?: string;
    errorDetails?: string;
  }> {
    const startTime = Date.now();
    let connection: mysql.PoolConnection | null = null;

    try {
      const pool = this.createPool();
      connection = await pool.getConnection();
      
      // 执行简单查询测试连接
      const [rows] = await connection.execute('SELECT VERSION() as version, NOW() as current_time');
      const result = rows as any[];
      
      const responseTime = Date.now() - startTime;
      
      return {
        success: true,
        message: '连接测试成功',
        responseTime,
        serverVersion: result[0]?.version || 'Unknown'
      };
    } catch (error: any) {
      const responseTime = Date.now() - startTime;
      
      return {
        success: false,
        message: '连接测试失败',
        responseTime,
        errorDetails: error.message
      };
    } finally {
      if (connection) {
        connection.release();
      }
    }
  }

  // 执行查询
  async executeQuery(sql: string, params: any[] = []): Promise<QueryResult> {
    let connection: mysql.PoolConnection | null = null;
    
    try {
      const pool = this.createPool();
      connection = await pool.getConnection();
      
      const [rows, fields] = await connection.execute(sql, params);
      
      // 处理不同类型的查询结果
      if (Array.isArray(rows)) {
        return {
          rows: rows as any[],
          fields: fields as any[]
        };
      } else {
        // INSERT, UPDATE, DELETE等操作
        const result = rows as mysql.ResultSetHeader;
        return {
          rows: [],
          fields: [],
          affectedRows: result.affectedRows,
          insertId: result.insertId,
          warningCount: (result as any).warningCount
        };
      }
    } catch (error: any) {
      throw new Error(`查询执行失败: ${error.message}`);
    } finally {
      if (connection) {
        connection.release();
      }
    }
  }

  // 获取数据库列表
  async getDatabases(): Promise<string[]> {
    try {
      const result = await this.executeQuery('SHOW DATABASES');
      return result.rows.map(row => row.Database);
    } catch (error: any) {
      throw new Error(`获取数据库列表失败: ${error.message}`);
    }
  }

  // 获取表列表
  async getTables(database?: string): Promise<Array<{
    table_name: string;
    table_type: string;
    table_comment: string;
  }>> {
    try {
      let sql = `
        SELECT 
          TABLE_NAME as table_name,
          TABLE_TYPE as table_type,
          TABLE_COMMENT as table_comment
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = ?
        ORDER BY TABLE_NAME
      `;
      
      const dbName = database || this.config.database;
      if (!dbName) {
        throw new Error('未指定数据库名称');
      }
      
      const result = await this.executeQuery(sql, [dbName]);
      return result.rows;
    } catch (error: any) {
      throw new Error(`获取表列表失败: ${error.message}`);
    }
  }

  // 获取表结构
  async getTableSchema(tableName: string, database?: string): Promise<TableSchema> {
    try {
      const dbName = database || this.config.database;
      if (!dbName) {
        throw new Error('未指定数据库名称');
      }

      const sql = `
        SELECT 
          COLUMN_NAME as column_name,
          DATA_TYPE as data_type,
          IS_NULLABLE as is_nullable,
          COLUMN_DEFAULT as column_default,
          COLUMN_COMMENT as column_comment
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
        ORDER BY ORDINAL_POSITION
      `;
      
      const result = await this.executeQuery(sql, [dbName, tableName]);
      
      return {
        table_name: tableName,
        columns: result.rows.map(row => ({
          column_name: row.column_name,
          data_type: row.data_type,
          is_nullable: row.is_nullable === 'YES',
          column_default: row.column_default,
          column_comment: row.column_comment || ''
        }))
      };
    } catch (error: any) {
      throw new Error(`获取表结构失败: ${error.message}`);
    }
  }

  // 获取数据库完整结构
  async getDatabaseSchema(database?: string): Promise<DatabaseSchema> {
    try {
      const dbName = database || this.config.database;
      if (!dbName) {
        throw new Error('未指定数据库名称');
      }

      const tables = await this.getTables(dbName);
      
      return {
        database_name: dbName,
        tables
      };
    } catch (error: any) {
      throw new Error(`获取数据库结构失败: ${error.message}`);
    }
  }

  // 获取连接池状态
  getPoolStatus(): {
    totalConnections: number;
    activeConnections: number;
    idleConnections: number;
    queuedRequests: number;
  } {
    if (!this.pool) {
      return {
        totalConnections: 0,
        activeConnections: 0,
        idleConnections: 0,
        queuedRequests: 0
      };
    }

    // mysql2连接池状态信息
    const pool = this.pool as any;
    
    return {
      totalConnections: pool._allConnections?.length || 0,
      activeConnections: pool._acquiringConnections?.length || 0,
      idleConnections: pool._freeConnections?.length || 0,
      queuedRequests: pool._connectionQueue?.length || 0
    };
  }

  // 关闭连接池
  async close(): Promise<void> {
    if (this.pool) {
      await this.pool.end();
      this.pool = null;
      console.log('Doris连接池已关闭');
    }
  }

  // 执行事务
  async executeTransaction(queries: Array<{ sql: string; params?: any[] }>): Promise<QueryResult[]> {
    let connection: mysql.PoolConnection | null = null;
    
    try {
      const pool = this.createPool();
      connection = await pool.getConnection();
      
      await connection.beginTransaction();
      
      const results: QueryResult[] = [];
      
      for (const query of queries) {
        const [rows, fields] = await connection.execute(query.sql, query.params || []);
        
        if (Array.isArray(rows)) {
          results.push({
            rows: rows as any[],
            fields: fields as any[]
          });
        } else {
          const result = rows as mysql.ResultSetHeader;
          results.push({
            rows: [],
            fields: [],
            affectedRows: result.affectedRows,
            insertId: result.insertId,
            warningCount: (result as any).warningCount
          });
        }
      }
      
      await connection.commit();
      return results;
    } catch (error: any) {
      if (connection) {
        await connection.rollback();
      }
      throw new Error(`事务执行失败: ${error.message}`);
    } finally {
      if (connection) {
        connection.release();
      }
    }
  }

  // 检查连接是否健康
  async isHealthy(): Promise<boolean> {
    try {
      const result = await this.testConnection();
      return result.success;
    } catch {
      return false;
    }
  }
}